#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
import sys

class sqlManager:
    ## sql info
    host = ""
    user = ""
    passwd = ""
    db = ""
    
    ## Executes a given query
    def sqlExec(self, query, getReturn=False):
        try:
            self.cursor.execute(query)
            if(getReturn):
                return self.cursor.fetchall()
            ##print "Number of rows updated: %d" % self.cursor.rowcount
        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            sys.exit (1)  
            
    def insertPlayer(self, player):
        query = ("""INSERT INTO players (id, position, firstname, lastname, team, score, assist, stats,
        pentalybox, price, saving, nrofteams, totalscoreforteams) VALUES (null, '""" + player['position'] + """','""" + player['firstname'] + """','""" + player['lastname'] + """','"""
        + player['team'] + """','""" + player['score'] + """','""" + player['assist'] + """','""" + player['stats'] + """','""" + player["penalty"] + """','2000','""" + player['saving'] + """','0','0')""")
        ##print query
        self.sqlExec(query)

    def playerScoreUpdate(self, player):
        print "Updateing player: " + player['firstname'] + ", " + player['lastname']
        query = "SELECT * FROM players WHERE firstname='" + player['firstname'] + "' AND lastname='" + player['lastname'] + "'"
        tmp = self.sqlExec(query, True)
        sum = tmp[0][12]
       ##an if to add the player if we cant find him
        if(len(tmp) == 0):
            self.insertPlayer(player)
            return
            
        newGoals = tmp[0][5]
        newAssists = tmp[0][6]
        newStats = tmp[0][7]
        newPentalybox = tmp[0][11]
        score = (long(player['score']) -newGoals) + (long(player['assist']) - newAssists) + (long(player['stats'])-newStats) - 0.1*((long(player['penalty']))-newPentalybox)
        query = "SELECT * from teams WHERE " + str(player['position']) + "=" + str(tmp[0][0])
        teams = self.sqlExec(query, True)

        for team in teams:
            query = "SELECT points FROM teams WHERE id=" + str(team[0])
            oldscore = self.sqlExec(query, True)
            newscore = oldscore[0][0] + score
            query = """UPDATE teams set points='""" + str(newscore) + """' WHERE id = """ + str(team[0])
            self.sqlExec(query)
            sum += score
        query = """UPDATE players SET team='""" + str(player['team']) + """', totalscoreforteams='""" + str(sum) + """', score='""" + str(player['score']) + """', assist='""" + str(player['assist']) + """', stats='""" + str(player['stats']) + """', pentalybox='""" + str(player['penalty']) + """' WHERE id='""" + str(tmp[0][0]) + """'"""
        self.sqlExec(query)

        
    
    def updateGoalie(self, player): 
        query = ("""UPDATE players SET saving ='""" + player['saving'] + """' WHERE firstname='""" + player['firstname'] + """' AND lastname='""" + player['lastname'] + """'""")
        self.sqlExec(query)
             
    def updatePlayer(self, player):
        query = ""
        sqlExec(query)
        
    def __init__(self):
        try:
            self.conn = MySQLdb.connect (self.host, self.user, self.passwd, self.db)
            self.cursor = self.conn.cursor ()
        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            sys.exit (1)  
        
    def testSQL(self):
         query = "SELECT score FROM players WHERE team='AIK' AND id=211111"
         kaka2 = self.sqlExec(query, True)
         print len(kaka2)
        
